| ASP.NET MVC5+EF6+EasyUI 后台管理系统（81） | 您所在的位置：网站首页 › easyui formatter › ASP.NET MVC5+EF6+EasyUI 后台管理系统（81） |
ASP.NET MVC5+EF6+EasyUI 后台管理系统（81）
|
前言 听标题的名字似乎是一个非常牛X复杂的功能，但是实际上它确实是非常复杂的，我们本节将演示如何实现对数据，进行组合查询（数据筛选） 我们都知道Excel中是如何筛选数据的.就像下面一样
他是一个并的关系，我们现在要做的也是这样的效果，下面我们将利用EasyUI的DataGrid为例来扩展（就算是其他组件也是可以的，同样的实现方式！） 实现思路 1.前台通过查询组合json 2.后台通过反射拆解json 3.进行组合查询 虽然短短3点，够你写个3天天夜了 优点：需要从很多数据中得到精准的数据，通常查一些商品他们的属性异常接近的情况下使用 缺点：我实现的方式为伪查询，大量数据请使用存储过程 简单了解 从Easyui的官方扩展中了解到一个JS文件，但是实质上，这个文件BUG很多，在使用中我曾经一度认为是使用出现问题，其实他根本就不可用
所以我这里先献上修改后的整个JS代码 (function($){ function getPluginName(target){ if ($(target).data('treegrid')){ return 'treegrid'; } else { return 'datagrid'; } } var autoSizeColumn1 = $.fn.datagrid.methods.autoSizeColumn; var loadDataMethod1 = $.fn.datagrid.methods.loadData; var appendMethod1 = $.fn.datagrid.methods.appendRow; var deleteMethod1 = $.fn.datagrid.methods.deleteRow; $.extend($.fn.datagrid.methods, { autoSizeColumn: function(jq, field){ return jq.each(function(){ var fc = $(this).datagrid('getPanel').find('.datagrid-header .datagrid-filter-c'); fc.hide(); autoSizeColumn1.call($.fn.datagrid.methods, $(this), field); fc.show(); resizeFilter(this, field); }); }, loadData: function(jq, data){ jq.each(function(){ $.data(this, 'datagrid').filterSource = null; }); return loadDataMethod1.call($.fn.datagrid.methods, jq, data); }, appendRow: function(jq, row){ var result = appendMethod1.call($.fn.datagrid.methods, jq, row); jq.each(function(){ var state = $(this).data('datagrid'); if (state.filterSource){ state.filterSource.total++; if (state.filterSource.rows != state.data.rows){ state.filterSource.rows.push(row); } } }); return result; }, deleteRow: function(jq, index){ jq.each(function(){ var state = $(this).data('datagrid'); var opts = state.options; if (state.filterSource && opts.idField){ if (state.filterSource.rows == state.data.rows){ state.filterSource.total--; } else { for(var i=0; i=0 ? state.filterSource.rows[index]._parentId : null; var rows = translateTreeData(this, [param.data], pid); var newRows = state.filterSource.rows.splice(0, index>=0 ? (param.before ? index : index+1) : (state.filterSource.rows.length)); newRows = newRows.concat(rows); newRows = newRows.concat(state.filterSource.rows); state.filterSource.total += rows.length; state.filterSource.rows = newRows; $(this).treegrid('loadData', state.filterSource); function getNodeIndex(id){ var rows = state.filterSource.rows; for(var i=0; i col.boxWidth+col.deltaWidth-1){ col.boxWidth = cc.width() - col.deltaWidth + 1; col.width = col.boxWidth + col.deltaWidth; toFixColumnSize = true; } }); if (toFixColumnSize){ $(target).datagrid('fixColumnSize'); } function _getContentWidth(cc){ var w = 0; $(cc).children(':visible').each(function(){ w += $(this)._outerWidth(); }); return w; } } function getFilterComponent(target, field){ var header = $(target).datagrid('getPanel').find('div.datagrid-header'); return header.find('tr.datagrid-filter-row td[field="'+field+'"] .datagrid-filter'); } /** * get filter rule index, return -1 if not found. */ function getRuleIndex(target, field){ var name = getPluginName(target); var rules = $(target)[name]('options').filterRules; for(var i=0; i= 0){ return rules[index]; } else { return null; } } function addFilterRule(target, param) { var name = getPluginName(target); var opts = $(target)[name]('options'); var rules = opts.filterRules; if (param.op == 'nofilter'){ removeFilterRule(target, param.field); } else { var index = getRuleIndex(target, param.field); if (index >= 0){ $.extend(rules[index], param); } else { rules.push(param); } } var input = getFilterComponent(target, param.field); if (input.length){ if (param.op != 'nofilter'){ input[0].filter.setValue(input, param.value); } var menu = input[0].menu; if (menu){ menu.find('.'+opts.filterMenuIconCls).removeClass(opts.filterMenuIconCls); var item = menu.menu('findItem', opts.operators[param.op]['text']); menu.menu('setIcon', { target: item.target, iconCls: opts.filterMenuIconCls }); } } } function removeFilterRule(target, field){ var name = getPluginName(target); var dg = $(target); var opts = dg[name]('options'); if (field){ var index = getRuleIndex(target, field); if (index >= 0){ opts.filterRules.splice(index, 1); } _clear([field]); } else { opts.filterRules = []; var fields = dg.datagrid('getColumnFields',true).concat(dg.datagrid('getColumnFields')); _clear(fields); } function _clear(fields){ for(var i=0; ia.Id.Contains(queryStr) || a.Name.Contains(queryStr) || a.Code.Contains(queryStr) || a.Color.Contains(queryStr) || a.CategoryId.Contains(queryStr) || a.CreateBy.Contains(queryStr) ); } else { queryData = m_Rep.GetList(); } //启用通用列头过滤 if (!string.IsNullOrWhiteSpace(pager.filterRules)) { List dataFilterList = JsonHandler.Deserialize(pager.filterRules).Where(f => !string.IsNullOrWhiteSpace(f.value)).ToList(); queryData = LinqHelper.DataFilter(queryData, dataFilterList); } pager.totalRows = queryData.Count(); //排序 queryData = LinqHelper.SortingAndPaging(queryData, pager.sort, pager.order, pager.page, pager.rows); return CreateModelList(ref queryData); } public override List CreateModelList(ref IQueryable queryData) { List modelList = (from r in queryData select new Spl_ProductModel { Id = r.Id, Name = r.Name, Code = r.Code, Price = r.Price, Color = r.Color, Number = r.Number, CategoryId = r.CategoryId, CreateTime = r.CreateTime, CreateBy = r.CreateBy, CostPrice = r.CostPrice, ProductCategory = r.Spl_ProductCategory.Name }).ToList(); return modelList; } } } //启用通用列头过滤 if (!string.IsNullOrWhiteSpace(pager.filterRules)) { List dataFilterList = JsonHandler.Deserialize(pager.filterRules).Where(f => !string.IsNullOrWhiteSpace(f.value)).ToList(); queryData = LinqHelper.DataFilter(queryData, dataFilterList); }其他都不变。 后台也是做了大量大量的工作的，看LinqHelper这个类 using System; using System.Collections.Generic; using System.Linq; using System.Linq.Expressions; using System.Reflection; using System.Text; using System.Threading.Tasks; namespace Apps.Common { public class LinqHelper { /// /// 排序 /// /// /// /// /// /// public static IQueryable DataSorting(IQueryable source, string sortExpression, string sortDirection) { //错误查询 if (string.IsNullOrEmpty(sortExpression) || string.IsNullOrEmpty(sortDirection)) { return source; } string sortingDir = string.Empty; if (sortDirection.ToUpper().Trim() == "ASC") sortingDir = "OrderBy"; else if (sortDirection.ToUpper().Trim() == "DESC") sortingDir = "OrderByDescending"; ParameterExpression param = Expression.Parameter(typeof(T), sortExpression); PropertyInfo pi = typeof(T).GetProperty(sortExpression); Type[] types = new Type[2]; types[0] = typeof(T); types[1] = pi.PropertyType; Expression expr = Expression.Call(typeof(Queryable), sortingDir, types, source.Expression, Expression.Lambda(Expression.Property(param, sortExpression), param)); IQueryable query = source.AsQueryable().Provider.CreateQuery(expr); return query; } /// /// 分页 /// /// /// /// /// /// public static IQueryable DataPaging(IQueryable source, int pageNumber, int pageSize) { if (pageNumber !string.IsNullOrWhiteSpace(f.value)); * queryData = LinqHelper.DataFilter(queryData.AsQueryable(), dataFilterList); * } */ //////通用数据列表按过滤方法 /// ///过滤的数据类型 ///过滤的数据源 ///过滤条件集合(包含,字段名,值,操作符) /// public static IQueryable DataFilter(IQueryable source, IEnumerable datas) { T obj = System.Activator.CreateInstance(); PropertyInfo[] properties = obj.GetType().GetProperties(); foreach (var item in datas) { PropertyInfo p = properties.Where(pro => pro.Name == item.field).FirstOrDefault(); //不进行无效过滤 if (p == null || item.value == null) { continue; } if (p.PropertyType == typeof(DateTime) || p.PropertyType == typeof(DateTime?)) { //时间过1滤 source = DateDataFilter(source, item, p); } else { //普通过滤 source = OrdinaryDataFilter(source, item, p); } } return source; } /// ///普通数据过滤 /// /// /// /// /// /// private static IQueryable OrdinaryDataFilter(IQueryable source, DataFilterModel item, PropertyInfo p) { //var selectvalue = Convert. // ChangeType(item.value, p.PropertyType); var option = (DataFliterOperatorTypeEnum) Enum.Parse(typeof(DataFliterOperatorTypeEnum), item.op); switch (option) { case DataFliterOperatorTypeEnum.contains: { /* 包含, 目前只支持字符串 */ source = ExpressionOperate(StringContains, source, p, item.value); break; } case DataFliterOperatorTypeEnum.equal: { /* 等于 */ source = ExpressionOperate(Expression.Equal, source, p, item.value); break; } case DataFliterOperatorTypeEnum.greater: { /* 大于 */ source = ExpressionOperate(Expression.GreaterThan, source, p, item.value); break; } case DataFliterOperatorTypeEnum.greaterorequal: { /* 大于等于 */ source = ExpressionOperate(Expression.GreaterThanOrEqual, source, p, item.value); break; } case DataFliterOperatorTypeEnum.less: { /* 小于 */ source = ExpressionOperate(Expression.LessThan, source, p, item.value); break; } case DataFliterOperatorTypeEnum.lessorequal: { /* 小于等于 */ source = ExpressionOperate(Expression.LessThanOrEqual, source, p, item.value); break; } default: break; } return (source); } /// ///时间过滤 /// /// /// /// /// /// public static IQueryable DateDataFilter(IQueryable source, DataFilterModel item, PropertyInfo p) { var selectDate= Convert.ToDateTime(item.value); var option= (DataFliterOperatorTypeEnum) Enum.Parse(typeof(DataFliterOperatorTypeEnum), item.op); switch(option) { case DataFliterOperatorTypeEnum.equal: { //大于0时 source=ExpressionOperate(Expression.GreaterThanOrEqual, source, p,selectDate); //小于后一天 var nextDate= selectDate.AddDays(1); source=ExpressionOperate(Expression.LessThan, source, p, nextDate); break; } case DataFliterOperatorTypeEnum.greater: { //大于等于后一天 selectDate= selectDate.AddDays(1); source=ExpressionOperate(Expression.GreaterThanOrEqual, source, p,selectDate); break; } case DataFliterOperatorTypeEnum.greaterorequal: { //大于等于当天 source=ExpressionOperate(Expression.GreaterThanOrEqual, source, p,selectDate); break; } case DataFliterOperatorTypeEnum.less: { //小于当天 source=ExpressionOperate(Expression. LessThan, source, p,selectDate); break; } case DataFliterOperatorTypeEnum.lessorequal: { //小于第二天 selectDate= selectDate.AddDays(1); source=ExpressionOperate(Expression. LessThan, source, p,selectDate); break; } default: break; } return source; } /// ///过滤操作 /// /// // /// /// /// /// /// private static IQueryable ExpressionOperate(ExpressionOpretaDelegate operateExpression, IQueryable source, PropertyInfo p, V value) { Expression right = null; if (p.PropertyType == typeof(Int32)) { int val = Convert.ToInt32(value); right = Expression.Constant(val, p.PropertyType); } else if (p.PropertyType == typeof(Decimal)) { Decimal val = Convert.ToDecimal(value); right = Expression.Constant(val, p.PropertyType); } else if (p.PropertyType == typeof(Byte)) { Byte val = Convert.ToByte(value); right = Expression.Constant(val, p.PropertyType); } else { right = Expression.Constant(value, p.PropertyType); } ParameterExpression param = Expression.Parameter(typeof(T), "x"); Expression left = Expression.Property(param, p.Name); Expression filter = operateExpression(left, right); Expression pred = Expression.Lambda(filter, param); source = source.Where(pred); return source; } /// ///字符串包含操作 /// /// /// /// public static Expression StringContains(Expression left, Expression right) { Expression filter = Expression.Call(left, typeof(string).GetMethod("Contains"), right); return filter; } } }预览效果：
总结 实现一个组合查询，只需要在原来的基础上添加几行代码 后台： //启用通用列头过滤 if (!string.IsNullOrWhiteSpace(pager.filterRules)) { List dataFilterList = JsonHandler.Deserialize(pager.filterRules).Where(f => !string.IsNullOrWhiteSpace(f.value)).ToList(); queryData = LinqHelper.DataFilter(queryData, dataFilterList); }前端: var dg = $('#List'); var op = ['equal', 'notequal', 'less', 'greater']; var comboData={Category:[]}; //[{ value: '', text: 'All' }, { value: 'P', text: 'P' }, { value: 'N', text: 'N' }] dg.datagrid('enableFilter', [ InitNumberFilter(dg, 'Price', op), InitNumberFilter(dg, 'Number', op), InitDateFilter(dg, 'CreateTime', op), InitComboFilter(dg, 'CategoryId', comboData, '../Spl/ProductCategory/GetComboxData', 'Id', 'Name', 'Name', "post") ]);完全没有任何逻辑，谁都能用，示例代码下载 http://pan.baidu.com/s/1dF409yx 以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持旗云源码。 MVC5 EF6 easyui |
【本文地址】
公司简介
联系我们